package com.ysd.Dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.News;
import com.ysd.util.DBmanager;



public class NewsDaoImpl {
	//方法的语法机构
		//访问修饰符 返回值类型 方法名(参数列表){方法体}
		//目的:查询所有用户信息
	public List<News> getNewsList(int cId){//通过新闻分类id查询新闻
		List<News> list = new ArrayList<News>();
		//1建立连接
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {//try正常执行
			//2创建statement执行对象
			st = conn.createStatement();
			//3SQL语句
			String sql = "select * from news where newsCategoryId = " +cId;
			//4执行SQL语句
			rs = st.executeQuery(sql);
			//5便利结果集
			while(rs.next()) {
				
				int newsId = rs.getInt("newsId");
				String newsName = rs.getString("newsName");
				String newsTime = rs.getString("newsTime");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				News news = new News(newsId, newsName, newsTime, newsContent, newsCategoryId);
				list.add(news);
			}
			return list;//正常情况下返回集合
		} catch (SQLException e) {//catch异常执行
			e.printStackTrace();
			return null;//异常情况下返回空
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}
	/**
	   * 根据新闻id查出指定id新闻
	 * @param nId
	 * @return
	 */
	public News getNewsById(int nId){
		//1建立连接
		News news =null;
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {//try正常执行
			//2创建statement执行对象
			st = conn.createStatement();
			//3SQL语句
			String sql = "select n.* ,nc.newsClassIficationName FROM news n , newsclassification nc where 1=1 and newsId = "+nId+" and nc.newsClassIficationId = n.newsCategoryId ";
			//4执行SQL语句
			rs = st.executeQuery(sql);
			//5便利结果集
			while(rs.next()) {
				
				int newsId = rs.getInt("newsId");
				String newsName = rs.getString("newsName");
				String newsTime = rs.getString("newsTime");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsClassIficationName = rs.getString("newsClassIficationName");
				 news = new News(newsId, newsName, newsTime, newsContent, newsCategoryId, newsClassIficationName);
			}
			return news;//正常情况下返回集合
		} catch (SQLException e) {//catch异常执行
			e.printStackTrace();
			return null;//异常情况下返回空
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}

	/**
	 * 根据新闻id查出上一条新闻
	 * @param bId
	 * @return
	 */
	public List<News> getNewsBefore(int bId){
		List<News> list = new ArrayList<News>();
		//1建立连接
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {//try正常执行
			//2创建statement执行对象
			st = conn.createStatement();
			//3SQL语句
			String sql ="SELECT * FROM news  WHERE newsId  <(SELECT newsId FROM news where newsId="+bId+")  ORDER BY newsId desc LIMIT 1";
			//4执行SQL语句
			rs = st.executeQuery(sql);
			//5便利结果集
			while(rs.next()) {
				
				int newsId = rs.getInt("newsId");
				String newsName = rs.getString("newsName");
				String newsTime = rs.getString("newsTime");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				News news = new News(newsId, newsName, newsTime, newsContent, newsCategoryId);
				list.add(news);
			}
			return list;//正常情况下返回集合
		} catch (SQLException e) {//catch异常执行
			e.printStackTrace();
			return null;//异常情况下返回空
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 根据新闻id查出下一条新闻
	 * @param bId
	 * @return
	 */
	public List<News> getNewsNext(int bId){
		List<News> list = new ArrayList<News>();
		//1建立连接
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {//try正常执行
			//2创建statement执行对象
			st = conn.createStatement();
			//3SQL语句     
			String sql = "SELECT * FROM news  WHERE newsId  >(SELECT newsId FROM news where newsId="+bId+")  ORDER BY newsId LIMIT 1";
			//4执行SQL语句
			rs = st.executeQuery(sql);
			//5便利结果集
			while(rs.next()) {
				
				int newsId = rs.getInt("newsId");
				String newsName = rs.getString("newsName");
				String newsTime = rs.getString("newsTime");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				News news = new News(newsId, newsName, newsTime, newsContent, newsCategoryId);
				list.add(news);
			}
			return list;//正常情况下返回集合
		} catch (SQLException e) {//catch异常执行
			e.printStackTrace();
			return null;//异常情况下返回空
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 查询全部新闻
	 * @return
	 */
	public List<News> getNewsListAll(){
		List<News> list = new ArrayList<News>();
		//1建立连接
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {//try正常执行
			//2创建statement执行对象
			st = conn.createStatement();
			//3SQL语句
			String sql = "select * from news";
			//4执行SQL语句
			rs = st.executeQuery(sql);
			//5便利结果集
			while(rs.next()) {
				
				int newsId = rs.getInt("newsId");
				String newsName = rs.getString("newsName");
				String newsTime = rs.getString("newsTime");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				News news = new News(newsId, newsName, newsTime, newsContent, newsCategoryId);
				list.add(news);
			}
			return list;//正常情况下返回集合
		} catch (SQLException e) {//catch异常执行
			e.printStackTrace();
			return null;//异常情况下返回空
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 
	 * 根据新闻前台传来的参数查询新闻,带条件查询
	 * n前台传来的参数
	 * @author 86176
	 *return 返回集合
	 */
	public List<News> getNews(News n,int page, int size ){
		List<News> list = new ArrayList<News>();
		//1建立连接
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {//try正常执行
			//2创建statement执行对象
			st = conn.createStatement();
			//3SQL语句
			String sql = "select n.* ,nc.newsClassIficationName FROM news n , newsclassification nc  where 1=1";
			//非空判断
			if(n.getNewsName()!=null && !n.getNewsName().equals("")) {
				sql+="  and newsName like '%"+n.getNewsName()+"%' ";
			}
			if(n.getNewsCategoryId()>0) {
				sql+=" and newsCategoryId ="+n.getNewsCategoryId();
			}
				sql += " and nc.newsClassIficationId = n.newsCategoryId order by newsId desc limit "+page+","+size;
			System.out.println(sql);//输出SQL语句以便查看SQL语句是否有误
			//4执行SQL语句
			rs = st.executeQuery(sql);
			//5便利结果集
			while(rs.next()) {
				
				int newsId = rs.getInt("newsId");
				String newsName = rs.getString("newsName");
				String newsTime = rs.getString("newsTime");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsClassIficationName = rs.getString("newsClassIficationName");
				News news = new News(newsId, newsName, newsTime, newsContent, newsCategoryId, newsClassIficationName);
				list.add(news);
			}
			return list;//正常情况下返回集合
		} catch (SQLException e) {//catch异常执行
			e.printStackTrace();
			return null;//异常情况下返回空
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
		
		
	}
	
	/**
	 * 
	 * 查询新闻的条数
	 * n前台传来的参数
	 * @author 86176
	 *return 返回集合
	 */
	public List<News> getAllNews(News n){
		List<News> list = new ArrayList<News>();
		//1建立连接
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {//try正常执行
			//2创建statement执行对象
			st = conn.createStatement();
			//3SQL语句
			String sql = "select n.* ,nc.newsClassIficationName FROM news n , newsclassification nc  where 1=1";
			//非空判断
			if(n.getNewsName()!=null && !n.getNewsName().equals("")) {
				sql+="  and newsName like '%"+n.getNewsName()+"%' ";
			}
			if(n.getNewsCategoryId()>0) {
				sql+=" and newsCategoryId ="+n.getNewsCategoryId();
			}
				sql += " and nc.newsClassIficationId = n.newsCategoryId";
			System.out.println(sql);//输出SQL语句以便查看SQL语句是否有误
			//4执行SQL语句
			rs = st.executeQuery(sql);
			//5便利结果集
			while(rs.next()) {
				
				int newsId = rs.getInt("newsId");
				String newsName = rs.getString("newsName");
				String newsTime = rs.getString("newsTime");
				String newsContent = rs.getString("newsContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsClassIficationName = rs.getString("newsClassIficationName");
				News news = new News(newsId, newsName, newsTime, newsContent, newsCategoryId, newsClassIficationName);
				list.add(news);
			}
			return list;//正常情况下返回集合
		} catch (SQLException e) {//catch异常执行
			e.printStackTrace();
			return null;//异常情况下返回空
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
		
	}
	
/**
 * 根据新闻名称查出指定名称是否存在
 * @param name
 * @return
 */
		public News getNewsByName(String name){
			//1建立连接
			News news = null;
			Connection conn = DBmanager.getConnection();
			Statement st = null;
			ResultSet rs = null;
			try {//try正常执行
				//2创建statement执行对象
				st = conn.createStatement();
				//3SQL语句
				String sql = "SELECT * FROM news  WHERE newsName='" +name+"' ";
				//4执行SQL语句
				rs = st.executeQuery(sql);
				//5便利结果集
				while(rs.next()) {
					
					int newsId = rs.getInt("newsId");
					String newsName = rs.getString("newsName");
					String newsTime = rs.getString("newsTime");
					String newsContent = rs.getString("newsContent");
					int newsCategoryId = rs.getInt("newsCategoryId");
					 news = new News(newsId, newsName, newsTime, newsContent, newsCategoryId);
				}
				return news;//正常情况下返回集合
			} catch (SQLException e) {//catch异常执行
				e.printStackTrace();
				return null;//异常情况下返回空
			} finally {
				DBmanager.closeAll(conn, st, rs);
			}
		}

	
	
	/**
	 * 
	 * 根据新闻id删除新闻
	 * nId前台传来的参数
	 * @author 86176
	 *return 返回受影响行数
	 */
	public int deleteNewsById(int nId) {
		return DBmanager.executeUpdate("DELETE FROM news where newsId ="+nId);
	}
	
	/**
	 * 
	 * 根据新闻id批量删除新闻
	 * nId前台传来的参数
	 * @author 86176
	 *return 返回受影响行数
	 */
	public int deleteNewsByIds(String nIds) {
		String sql = "DELETE FROM news where newsId in("+nIds+") ";
		System.out.println(sql);
		return DBmanager.executeUpdate(sql);
	}
	/**
	 * 
	 * 添加新闻
	 * nId前台传来的参数
	 * @author 86176
	 *return 返回受影响行数
	 */
	public int addNews(News news) {
		String	sql = "insert into news  VALUES(null,'"+news.getNewsName()+"','"+news.getNewsTime()+"','"+news.getNewsContent()+"','"+news.getNewsCategoryId()+"') ";
		System.out.println(sql);
		return DBmanager.executeUpdate(sql);
	}
	/**
	 * 
	 * 编辑新闻
	 * nId前台传来的参数
	 * @author 86176
	 *return 返回受影响行数
	 */
	public int editNews(News news) {
		String	sql = "update news set newsName='"+news.getNewsName()+"', newsCategoryId ='"+news.getNewsCategoryId()+" ',newsTime='"+news.getNewsTime()+"',newsContent='"+news.getNewsContent()+"' where newsId='"+news.getNewsId()+"' ";
		System.out.println(sql);
		return DBmanager.executeUpdate(sql);
	}
}
